package edu.qd.learn.newchapter.chapter05.execl;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 学习如何用java 操作execl
 *
 * @author 布衣
 */
public class OperationExcel {
    //社招
//    a.txt (inf) b.txt(inf)  sum.txt

    /**
     * 将 list里面的数据写入到execl中
     * @param pathName  文件地址
     * @param list      数据
     *                  [
     *                  {name:'张三',age:18,gender:'男'},
     *                  {name:'李四',age:18,gender:'男'},
     *                  {name:'王五',age:18,gender:'男'},
     *                  ......
     *                  ]
     */
    public static void writeListToExcel(String pathName, List<Map<String,Object>> list){
        assert pathName != null && !pathName.isEmpty();
        assert list != null && !list.isEmpty();

        try(
                FileInputStream fis = new FileInputStream(pathName);
                Workbook wk = new XSSFWorkbook(fis)
            ){
            XSSFSheet sheetAt = (XSSFSheet)wk.getSheetAt(0);

//            XSSFRow header = sheetAt.createRow(0);
//            header.createCell(0).setCellValue("姓名");
//            header.createCell(1).setCellValue("年龄");
//            header.createCell(2).setCellValue("性别");

            int size = 2;
            for (Map<String, Object> stringObjectMap : list) {

                //修改原有sheet名称
                XSSFRow row = sheetAt.createRow(size++);

                XSSFCell name = row.createCell(0);
                name.setCellValue(stringObjectMap.get("name").toString());

                CellStyle xcs = wk.createCellStyle();
                xcs.setLeftBorderColor((short)23423);
                xcs.setBorderLeft(BorderStyle.DOTTED);
                xcs.setAlignment(HorizontalAlignment.CENTER);
                name.setCellStyle(xcs);


                XSSFCell age = row.createCell(1);
                age.setCellValue(stringObjectMap.get("age").toString());

                XSSFCell gender = row.createCell(2);
                gender.setCellValue(stringObjectMap.get("gender").toString());
            }

            try (FileOutputStream fileOut = new FileOutputStream(pathName)) {
                wk.write(fileOut);
            } catch (IOException e) {
                e.printStackTrace();
            }

        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }


    public static void openExcel(String pathName) {
        System.out.println("打开电脑当中某一个excel文件");


        assert pathName != null && !pathName.isEmpty();

        try (
                //xlsx 5  xls 4
//                 "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
                //打文件，转换成fis
                FileInputStream fis = new FileInputStream(pathName);
                Workbook wk = new XSSFWorkbook(fis)
        ) {

            XSSFSheet sheet = (XSSFSheet) wk.getSheetAt(wk.getSheetIndex("测试"));

            XSSFRow row = sheet.createRow(0);
            XSSFCell cell1 = row.createCell(0);
            cell1.setCellValue("你好你好");

            //把修改以后的数据 写回到file
            // 将修改后的工作簿写回到文件
            try (FileOutputStream fileOut = new FileOutputStream(pathName)) {
                wk.write(fileOut);
            } catch (IOException e) {
                e.printStackTrace();
            }

        } catch ( IOException e) {
            throw new RuntimeException(e);
        }
    }

    //打开电脑当中某一个excel文件

    //是否可向里面写入文本

    public static void main(String[] args) {

        List<Map<String,Object>> stus = new ArrayList<>();

        Map<String,Object> stu1 = new HashMap<>();
        stu1.put("name","张三");
        stu1.put("age",18);
        stu1.put("gender","男");
        stus.add(stu1);

        stu1 = new HashMap<>();
        stu1.put("name","李四");
        stu1.put("age",18);
        stu1.put("gender","男");
        stus.add(stu1);

        stu1 = new HashMap<>();
        stu1.put("name","王五");
        stu1.put("age",18);
        stu1.put("gender","男");
        stus.add(stu1);

        writeListToExcel("C:\\Users\\lovex\\Desktop\\新建 XLSX 工作表 (2).xlsx",stus);

//        openExcel("C:\\Users\\lovex\\Desktop\\新建 XLSX 工作表 (2).xlsx");
    }

}
